package com.g2room.search.es.biz;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.g2room.search.DbUtils;
import com.g2room.search.es.engine.Index;
import com.g2room.search.es.po.TogImages;
import com.g2room.search.es.po.TogPost;
import com.g2room.utils.DateUtil;

public class IndexTableFromDb {
	static {  
		JSON.DEFFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
	}  
	
	static Index index = new Index("myuall");
	static Connection connection = DbUtils.getMyuallConn();
	public IndexTableFromDb() {
		DbUtils.closeConn(connection);
		// 获取连接
		connection = DbUtils.getMyuallConn();
	}
	public static void main(String[] args) {
		try {
			indexImgData();
			indexPostData();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtils.closeConn(connection);
	}
	
	public static void indexImgData() throws SQLException {
		String querySql = "select * from tog_images where syncTime> '2016-10-30 16:19:01'";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(querySql);
		List<String> ids = new ArrayList<String>();
		List<String> vals = new ArrayList<String>();
		while (rs.next()) {
			TogImages post = new TogImages();
			post.setId(rs.getInt("id")); 
			
			post.setCatid(rs.getInt("catId"));
			post.setCatname(rs.getString("catName"));
			post.setPosition(rs.getInt("position"));
			post.setUpdatetime(rs.getTimestamp("updateTime"));
			post.setProvinceId(rs.getInt("province"));
			post.setProvinceName(rs.getString("provinceName"));
			post.setCityId(rs.getInt("city"));
			post.setCityName(rs.getString("cityName"));
			post.setForumId(rs.getInt("forumId"));
			post.setForumUrl(rs.getString("forumUrl"));
			post.setForumName(rs.getString("forumName"));
			post.setSynctime(DateUtil.date2String(rs.getTimestamp("syncTime"), "yyyy-MM-dd HH:mm:ss"));
			post.setStatus(rs.getInt("status"));
			post.setAuthorName(rs.getString("author"));
			post.setAuthorId(rs.getInt("authorId"));
			post.setAuthorUrl(rs.getString("authorUrl"));
			post.setImageUrl(rs.getString("imageUrl"));
			post.setTitle(rs.getString("title"));
			post.setMessage(rs.getString("message"));
			post.setTid(rs.getInt("tid"));
			
			ids.add(post.getId()+"");
			vals.add(JSON.toJSONString(post, SerializerFeature.WriteDateUseDateFormat));
		}
		
		rs.close();
		index.createIndexBatch(ids, vals, "togImages");
		System.out.println("togImages数据总数=" + ids.size());
	}
	

	public static void indexPostData() throws SQLException {
		String querySql = "select * from tog_post where syncTime> '2016-10-30 16:19:01'";
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(querySql);

		List<String> ids = new ArrayList<String>();
		List<String> vals = new ArrayList<String>();
		while (rs.next()) {
			TogPost post = new TogPost();
			post.setId(rs.getInt("id"));
			ids.add(post.getId() + "");
			post.setCategoryId(rs.getInt("categoryId"));
			post.setCategoryName(rs.getString("categoryName"));
			post.setTid(rs.getInt("tid"));
			post.setFid(rs.getInt("fid"));
			post.setAuthorName(rs.getString("author"));
			post.setAuthorId(rs.getInt("authorId"));
			post.setAuthorUrl(rs.getString("authorUrl"));
			post.setTitle(rs.getString("subject"));
			post.setDateline(rs.getTimestamp("dateline"));
			post.setViews(rs.getInt("views"));
			post.setReplies(rs.getInt("replies"));
			post.setStick(rs.getInt("stick"));
			post.setHighlight(rs.getInt("highlight"));
			post.setDigest(rs.getInt("digest"));
			post.setStatus(rs.getInt("status"));
			post.setUrl(rs.getString("url"));
			post.setForumId(rs.getInt("forumId"));
			post.setForumUrl(rs.getString("forumUrl"));
			post.setForumName(rs.getString("forumName"));
			post.setProvinceId(rs.getInt("province"));
			post.setProvinceName(rs.getString("provinceName"));
			post.setCityId(rs.getInt("city"));
			post.setCityName(rs.getString("cityName"));
			post.setSynctime(rs.getString("syncTime"));
			post.setSummary(rs.getString("summary"));
			post.setShowSummary(rs.getInt("showSummary"));
			post.setContent(rs.getString("message"));
			
			vals.add(JSON.toJSONString(post, SerializerFeature.WriteDateUseDateFormat));
			if(vals.size() == 5000) {
				System.out.print("5000数据");
				index.createIndexBatch(ids, vals, "togPost");
				vals.clear();
				ids.clear();
			}
		}
		
		rs.close();
		index.createIndexBatch(ids, vals, "togPost");
		System.out.println("togPost数据总数=" + ids.size());
	}
	

//	public static void indexArtiData() {
//		String querySql = "select * from tog_images where syncTime>'2016-8-17'";
//		Statement statement = connection.createStatement();
//		ResultSet rs = statement.executeQuery(querySql);
//		List<TogImages> images  = new LinkedList<TogImages>();
//		System.out.println("本次同步图片表" + i + "语句：" + querySql);
//		while (rs.next()) {
//			TogImages post = new TogImages();
//			post.setId(rs.getInt("id")); 
//			
//			post.setCatid(rs.getInt("catId"));
//			post.setCatname(rs.getString("catName"));
//			post.setPosition(rs.getInt("position"));
//			post.setUpdatetime(rs.getTimestamp("updateTime"));
//			post.setProvinceId(rs.getInt("province"));
//			post.setProvinceName(rs.getString("provinceName"));
//			post.setCityId(rs.getInt("city"));
//			post.setCityName(rs.getString("cityName"));
//			post.setForumId(rs.getInt("forumId"));
//			post.setForumUrl(rs.getString("forumUrl"));
//			post.setForumName(rs.getString("forumName"));
//			post.setSynctime(rs.getString("syncTime"));
//			post.setStatus(rs.getInt("status"));
//			post.setAuthorName(rs.getString("author"));
//			post.setAuthorId(rs.getInt("authorId"));
//			post.setAuthorUrl(rs.getString("authorUrl"));
//			post.setImageUrl(rs.getString("imageUrl"));
//			post.setTitle(rs.getString("title"));
//			post.setMessage(rs.getString("message"));
//			post.setTid(rs.getInt("tid"));
//			images.add(post);
//		}
//		
//		rs.close();
//	}
}
